﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using BussModel;
using System.Web.Security;
using System.Security.Cryptography;

namespace DataBase
{
    /// <summary>
    /// Register 的摘要描述
    /// </summary>
    public class Register
    {
        private static Register instance = null;
        private string connectionString = Configuration.Instance.DBConntionString;

        public Register()
        {
            //
            // TODO: 在此加入建構函式的程式碼
            //
        }

        public static Register Instance
        {
            get
            {
                if (instance == null)
                {
                    instance = new Register();
                }
                return instance;
            }
        }
        /// <summary>
        /// 新增使用者的註冊資料
        /// </summary>
        /// <param name="model"></param>
        public void InsertRecord(RegisterModel model)
        {
            ParaModel.ParRegister param;
            int count = 0;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand sqlCommand = new SqlCommand())
                {
                    RNGCryptoServiceProvider salt = new RNGCryptoServiceProvider();
                   
                   
                    conn.Open();
                    sqlCommand.Connection = conn;
                     SqlTransaction myTrans =conn.BeginTransaction();
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.Transaction = myTrans;
                    sqlCommand.CommandText = @"
                        insert into Register 
                        (
                            Id, UserName,NickName,Sex,Salt,Password,AgainPassword,Email,
                            CellPhone,BirthDay,Constellation,Favorite,RegisterDateTime
                        )
                        values
                        (
                            @Id, @UserName, @NickName, @Sex,@Salt,@Password,@AgainPassword,
                            @Email, @CellPhone, @BirthDay, @Constellation,@Favorite,
                            @RegisterDateTime
                        )";
 
                    param = ParaModel.Instance.GetParaRegister(model);
                    sqlCommand.Parameters.Add(param.UserName);
                    sqlCommand.Parameters.Add(param.NickName);
                    sqlCommand.Parameters.Add(param.Sex);
                    sqlCommand.Parameters.Add(param.Salt);
                    sqlCommand.Parameters.Add(param.Password);
                    sqlCommand.Parameters.Add(param.AgainPassword);
                    sqlCommand.Parameters.Add(param.Email);
                    sqlCommand.Parameters.Add(param.CellPhone);
                    sqlCommand.Parameters.Add(param.BirthDay);
                    sqlCommand.Parameters.Add(param.Constellation);
                    sqlCommand.Parameters.Add(param.Favorite);
                    sqlCommand.Parameters.Add(param.Id);
                    sqlCommand.Parameters.Add(param.RegisterDateTime);

                    try
                    {
                         count = sqlCommand.ExecuteNonQuery();
                         myTrans.Commit();
                    }
                    catch (Exception ex)
                    {
                        myTrans.Rollback();
                        throw ex;
                    }
                    finally
                    {
                        model = null;
                    }

                }

            }

            //return new DataTable();
        }
        /// <summary>
        /// 更新會員的密碼
        /// </summary>
        /// <param name="model"></param>
        public void UpdatePasswordByUserNameAndFavoriteAndEmail(RegisterModel model)
        {
            ParaModel.ParRegister param;
            int count = 0;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand sqlCommand = new SqlCommand())
                {
                    conn.Open();
                    sqlCommand.Connection = conn;
                    SqlTransaction myTrans = conn.BeginTransaction();
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.Transaction = myTrans;
                    sqlCommand.CommandText = @"
                            update Register
                            set Password =@Password,
                            AgainPassword=@AgainPassword 
                            where UserName=@UserName And
                            Favorite=@Favorite And Email=@Email";
                    param = ParaModel.Instance.GetParaRegister(model);
                    sqlCommand.Parameters.Add(param.UserName);
                    sqlCommand.Parameters.Add(param.Favorite);
                    param.Password.Value = FormsAuthentication.HashPasswordForStoringInConfigFile(model.Password.ToString(), "sha1");
                    sqlCommand.Parameters.Add(param.Password);
                    param.AgainPassword.Value = FormsAuthentication.HashPasswordForStoringInConfigFile(model.AgainPassword.ToString(), "sha1");
                    sqlCommand.Parameters.Add(param.AgainPassword);
                    sqlCommand.Parameters.Add(param.Email);

                    try
                    {
                        count = sqlCommand.ExecuteNonQuery();
                        myTrans.Commit();
                    }
                    catch (Exception ex)
                    {
                        myTrans.Rollback();
                        throw ex;
                    }
                    finally
                    {
                        model = null;
                    }

                }

            }
        }
        /// <summary>
        /// 根據取得使用者帳號,取得註冊時所填寫的資料
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllRegisterDataById(string userName,string Password)
        {
            DataTable dt = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand sqlCommand = new SqlCommand())
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();

                    conn.Open();
                    sqlCommand.Connection = conn;
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText =
                    @"select  * from Register where UserName='"+userName+"'";
                    adapter.SelectCommand = sqlCommand;
                    dt = new DataTable();

                    try
                    {
                       adapter.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        adapter.Dispose();
                        adapter = null;
                    }
                }
            }

            return dt;
        }

        public DataSet GetFavorite()

        {
            DataSet ds = null;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand sqlCommand = new SqlCommand())
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();

                    conn.Open();
                    sqlCommand.Connection = conn;
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText =
                    @"select  *  from Favorite";
                    adapter.SelectCommand = sqlCommand;
                    ds = new DataSet();

                    try
                    {
                        adapter.Fill(ds, "RegisterTable");
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        adapter.Dispose();
                        adapter = null;
                    }
                }
            }

            return ds;
        }

       
        public DataSet GetRegisterDataByEmailAndFavorite(string UserName, string Favorite, string Email)
        {
            DataSet ds = null;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand sqlCommand = new SqlCommand())
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();

                    conn.Open();
                    sqlCommand.Connection = conn;
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText =
                     @"select  *  from Register where UserName='" + UserName.Trim() + "' and Favorite='" + Favorite.Trim() + "' and Email ='" + Email.Trim() + "'";
                    adapter.SelectCommand = sqlCommand;
                    ds = new DataSet();

                    try
                    {
                        adapter.Fill(ds, "RegisterTable");
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        adapter.Dispose();
                        adapter = null;
                    }
                }
            }

            return ds;
        }


        private bool ContainsUserName(string userName)
        {
            int count = 0;

            using ( SqlConnection conn = new SqlConnection(connectionString) )
            {
                using (SqlCommand sqlCommand = new SqlCommand())
                {
                    conn.Open();
                    sqlCommand.CommandType = CommandType.Text;
                    sqlCommand.CommandText =
                        @"select count(UserName) from Register where UserName='"+userName.Trim()+"'";

                    try
                    {
                        count = (int)sqlCommand.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                    }
                }
            }

            return (count > 0);
        }
    }
}